package com.mbbmap.app.home;

import java.util.*;
import java.sql.*;

import com.mbbmap.app.manager.*;
import com.mbbmap.security.dao.*;
import com.mbbmap.util.*;

import java.text.SimpleDateFormat;

public class SecGroupHome {
        private static SecGroupHome instance = null;

    /**
     * Returns the single instance, creating one if it's the
     * first time this method is called.
     *
     * @return IslamicContractHome. The single instance.
     */
        public static SecGroupHome getInstance() {
                //thread-safe implementation
                if (instance == null) {
                        synchronized (SecGroupHome.class) {
                                if (instance == null) {
                                        instance = new SecGroupHome();
                                }
                        }
                }
                return instance;
        }

        private SecGroupHome() {
                super();
        }


        /**
         *      Get Security Group List
         */
        public ArrayList findSecGroupList(String strMerchant) {
                                //System.out.println("********************"+newIcNo);

                String querySql = null;
                ArrayList secgroupList = new ArrayList();

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecGroupHome_findSecGroupList_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{

                                StringBuffer sqlStmt = new StringBuffer("");

                                //sqlStmt.append("select acct_name, is_contract,psr_rate from tblislamic_contract where acct_name='"+accountName+"'");
                                querySql = "select role_code,role_name,created_by, created_date from tblaccessrole where mer_name = '" + strMerchant
                                                        + "' order by role_name asc";
                                sqlStmt.append(querySql);
                                System.out.println("SecGroupHome.java: init findSecGroupList : " + sqlStmt.toString());

                                stmt = conn.prepareStatement(sqlStmt.toString());
                                //System.out.println("SecGroupHome.java findSecGroupList 01");

                                rs = stmt.executeQuery();
                                //System.out.println("SecGroupHome.java findSecGroupList 02");

                                while (rs.next()){
                                                //System.out.println("Resultset . next()");
                                                SecGroupsDao secgroupsDao = new SecGroupsDao();

                                                String strgroupCode = rs.getString(1);
                                                if (strgroupCode != null){
                                                        strgroupCode = strgroupCode.trim();
                                                }
                                                String strgroupName = rs.getString(2);
                                                if (strgroupName != null){
                                                        strgroupName = strgroupName.trim();
                                                }
                                                String strcreatedby = rs.getString(3);
                                                if (strcreatedby != null){
                                                        strcreatedby = strcreatedby.trim();
                                                }
                                                
                                                java.util.Date dCreateddt = rs.getDate(4);
                                                String strcreateddt = "";
                                                if (dCreateddt != null){
                                                    strcreateddt = DateUtil.formatLocalSlash(dCreateddt).trim();                                                    
                                                }
                                                
                                //System.out.println("TradeAcctHome.java findTradeAcctList client_code"+client_code);

                                                secgroupsDao.set(strgroupCode,strgroupName,strcreatedby,strcreateddt);
                                                secgroupList.add(secgroupsDao);
                                } //while (rs.next())
                                rs.close();
                                stmt.close();
                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("TradeAcctHome_findTradeAcct_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("TradeAcctHome_findTradeAcct_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }

                                //System.out.println("Resultset12 . next()");
                return secgroupList;
        }

        /**
         *      Get Security Module List
         */
        public ArrayList findSecModuleList(String strMerchant) {
                                //System.out.println("********************"+newIcNo);

                ArrayList secmoduleList = new ArrayList();

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecGroupHome_findSecGroupList_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{

                                StringBuffer sqlStmt = new StringBuffer("");

                                //sqlStmt.append("select acct_name, is_contract,psr_rate from tblislamic_contract where acct_name='"+accountName+"'");
                                sqlStmt.append("select module_code,module_name,created_by, created_date from tblaccessmodule where mer_name = '"+strMerchant+"' order by module_code, created_date");
                                //System.out.println("SecGroupHome.java: init findSecGroupList : " + sqlStmt);

                                stmt = conn.prepareStatement(sqlStmt.toString());

                                rs = stmt.executeQuery();
                                //System.out.println("Resultset . next()");

                                while (rs.next()){

                                                SecModulesDao secmodDao = new SecModulesDao();

                                                String strmoduleCode = rs.getString(1);
                                                if (strmoduleCode != null){
                                                        strmoduleCode = strmoduleCode.trim();
                                                }
                                                String strmoduleName = rs.getString(2);
                                                if (strmoduleName != null){
                                                        strmoduleName = strmoduleName.trim();
                                                }
                                                String strcreatedby = rs.getString(3);
                                                if (strcreatedby != null){
                                                        strcreatedby = strcreatedby.trim();
                                                }
                                                java.util.Date dCreateddt = rs.getDate(4);
                                                String strcreateddt = "";
                                                if (dCreateddt != null){
                                                    strcreateddt = DateUtil.formatLocalSlash(dCreateddt).trim();                                                    
                                                }
                                //System.out.println("TradeAcctHome.java findTradeAcctList client_code"+client_code);

                                                secmodDao.set(strmoduleCode,strmoduleName,strcreatedby,strcreateddt);
                                                secmoduleList.add(secmodDao);
                                } //while (rs.next())
                                rs.close();
                                stmt.close();

                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("TradeAcctHome_findTradeAcct_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("TradeAcctHome_findTradeAcct_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }
                return secmoduleList;
        }

        /**
         *      This is to add Security Group
         *      Author  :       Michael Kang
         *      Date    :       10/02/2009
         */
        public String addSecGroup(String strGroupCode,String strGroupName, String strLoginID, String strMerchant) {

                String strReturn = "N";

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecGroupHome_addSecGroup_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{
                                StringBuffer sqlStmt = new StringBuffer("");
                                if (strGroupCode != null && strGroupName != null && strLoginID != null && strMerchant != null){
                                        //NRIC,PAYEE_CODE,BILLER_CODE,ACCT_NO,PAYMENT_STATUS,BILL_AMT,PAY_AMT,BILL_DATE,DUE_DATE,
                                        java.util.Date creationDate = new java.util.Date();
                                        //String strCreationDate = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(creationDate);
                                        
                                        //System.out.println("BillAcctHome.java updatePayHistory strActionDate" + strActionDate);
                                        sqlStmt.append("INSERT INTO TBLACCESSROLE (MER_NAME,ROLE_CODE,ROLE_NAME,CREATED_BY,CREATED_DATE) VALUES ('" + strMerchant + "','" + strGroupCode + "','" + strGroupName + "','" + strLoginID + "', ? )");
                                        //System.out.println("SecGroupHome_addSecGroup_001 sqlStmt.toString()" + sqlStmt.toString());
                                        PreparedStatement insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.setTimestamp(1, new java.sql.Timestamp(creationDate.getTime()));
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        strReturn = "Y";
                                        //System.out.println("BillActHome Resultset . updatePayHistory()");
                                }else{
                                        strReturn = "N";
                                } // End if all parameters not null
                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecGroupHome_addSecGroup_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecGroupHome_addSecGroup_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }
                return strReturn;
        }

        /**
         *      This is to add Security Module
         *      Author  :       Michael Kang
         *      Date    :       10/02/2009
         */
        public String addSecModule(String strModuleCode,String strModuleName, String strLoginID, String strMerchant) {

                String strReturn = "N";

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecGroupHome_addSecModule_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{
                                StringBuffer sqlStmt = new StringBuffer("");
                                if (strModuleCode != null && strModuleName != null && strLoginID != null && strMerchant != null){
                                        //NRIC,PAYEE_CODE,BILLER_CODE,ACCT_NO,PAYMENT_STATUS,BILL_AMT,PAY_AMT,BILL_DATE,DUE_DATE,
                                        java.util.Date creationDate = new java.util.Date();
                                        //String strCreationDate = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(creationDate);
                                        //System.out.println("BillAcctHome.java updatePayHistory strActionDate" + strActionDate);
                                        //sqlStmt.append("INSERT INTO TBLACCESSMODULE (MER_NAME,MODULE_CODE,MODULE_NAME,CREATED_BY,CREATED_DATE) VALUES ('" + strMerchant + "','" + strModuleCode + "','" + strModuleName + "','" + strLoginID + "',to_date('"+strCreationDate+"','dd/MM/yyyy HH24:MI:SS'))");
                                        sqlStmt.append("INSERT INTO TBLACCESSMODULE ( MER_NAME , MODULE_CODE , MODULE_NAME , CREATED_BY , CREATED_DATE , CREATED_TIMESTAMP ) VALUES ('" + strMerchant + "','" + strModuleCode + "','" + strModuleName + "','" + strLoginID + "', SYSDATE , ? )");
                                        
                                        System.out.println("SecGroupHome_addSecModule_001 sqlStmt.toString()" + sqlStmt.toString());
                                        PreparedStatement insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.setTimestamp(1, new java.sql.Timestamp(creationDate.getTime()));
                                        System.out.println("SecGroupHome_addSecModule_001 insertPstmt.toString()" + insertPstmt.toString());
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        strReturn = "Y";
                                        System.out.println("Database TBLACCESSMODULE updated.");
                                        //System.out.println("BillActHome Resultset . updatePayHistory()");
                                }else{
                                        System.out.println("Database TBLACCESSMODULE not updated.");
                                        strReturn = "N";
                                } // End if all parameters not null
                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecGroupHome_addSecModule_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecGroupHome_addSecModule_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }
                return strReturn;
        }

        /**
         *      This is to delete Security Module
         *      Author  :       Michael Kang
         *      Date    :       10/02/2009
         */
        public String removeSecModule(String strModuleCode, String strLoginID, String strMerchant) {

                String strReturn = "N";

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecGroupHome_removeSecModule_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{
                                StringBuffer sqlStmt = new StringBuffer("");
                                if (strModuleCode != null && strLoginID != null && strMerchant != null){
                                        //NRIC,PAYEE_CODE,BILLER_CODE,ACCT_NO,PAYMENT_STATUS,BILL_AMT,PAY_AMT,BILL_DATE,DUE_DATE,
                                        java.util.Date creationDate = new java.util.Date();
                                        String strCreationDate = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(creationDate);
                                        //System.out.println("BillAcctHome.java updatePayHistory strActionDate" + strActionDate);
                                        sqlStmt.append("DELETE FROM TBLROLEPERMISSION WHERE MER_NAME = '" + strMerchant + "' AND ROLE_TYPE = '00' AND MODULE_CODE = '" + strModuleCode + "'");
                                        //System.out.println("SecGroupHome_removeSecModule_001 sqlStmt.toString()" + sqlStmt.toString());
                                        //stmt = conn.prepareStatement(sqlStmt.toString());
                                        PreparedStatement insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        sqlStmt = new StringBuffer("");
                                        sqlStmt.append("DELETE FROM TBLACCESSMODULE WHERE MER_NAME = '" + strMerchant + "' AND MODULE_CODE = '" + strModuleCode + "'");
                                        //System.out.println("SecGroupHome_removeSecModule_001 sqlStmt.toString()" + sqlStmt.toString());
                                        insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        strReturn = "Y";
                                        //System.out.println("BillActHome Resultset . updatePayHistory()");
                                }else{
                                        strReturn = "N";
                                } // End if all parameters not null
                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecGroupHome_removeSecModule_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecGroupHome_removeSecModule_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }
                return strReturn;
        }

        /**
         *      This is to delete Security Group
         *      
         *
         */
   public String removeSecGroup(String strGroupCode, String strLoginID, String strMerchant) {

                String strReturn = "N";

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecGroupHome_removeSecGroup_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{
                                StringBuffer sqlStmt = new StringBuffer("");
                                if (strGroupCode != null && strLoginID != null && strMerchant != null){
                                        //NRIC,PAYEE_CODE,BILLER_CODE,ACCT_NO,PAYMENT_STATUS,BILL_AMT,PAY_AMT,BILL_DATE,DUE_DATE,
                                        java.util.Date creationDate = new java.util.Date();
                                        String strCreationDate = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(creationDate);
                                        //System.out.println("BillAcctHome.java updatePayHistory strActionDate" + strActionDate);
                                        //sqlStmt.append("select a.biller_code, a.payee_code,a.acct_no,a.bill_ref1,a.status,a.act_flag,a.deact_flag, b.biller_name from tblbills a inner join tblbiller b on a.biller_code = b.biller_code where deact_flag <> '2' AND nric='"+newIcNo+"'");
                                        sqlStmt.append("DELETE FROM TBLROLEPERMISSION WHERE MER_NAME = '" + strMerchant + "' AND ROLE_TYPE = '00' AND ROLE_CODE = '" + strGroupCode + "'");
                                        //System.out.println("SecGroupHome_removeSecGroup_001 sqlStmt.toString()" + sqlStmt.toString());
                                        //stmt = conn.prepareStatement(sqlStmt.toString());
                                        PreparedStatement insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        sqlStmt = new StringBuffer("");
                                        sqlStmt.append("DELETE FROM TBLACCESSROLE WHERE MER_NAME = '" + strMerchant + "' AND ROLE_CODE = '" + strGroupCode + "'");
                                        //System.out.println("SecGroupHome_removeSecGroup_001 sqlStmt.toString()" + sqlStmt.toString());
                                        //stmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        
                                        sqlStmt = new StringBuffer("");
                                        sqlStmt.append("DELETE FROM TBLTREE WHERE MER_NAME = '" + strMerchant + "' AND (CHILD_ID = '" + strGroupCode + "' OR PARENT_ID = '" + strGroupCode + "')");
                                        //System.out.println("SecGroupHome_removeSecGroup_001 sqlStmt.toString()" + sqlStmt.toString());
                                        //stmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        
                                        strReturn = "Y";
                                        //System.out.println("BillActHome Resultset . updatePayHistory()");
                                }else{
                                        strReturn = "N";
                                } // End if all parameters not null
                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecGroupHome_removeSecModule_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecGroupHome_removeSecModule_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }
                return strReturn;
        }

}